package njust.edu.dao.impl;

import njust.edu.dao.TradeDao;
import njust.edu.dbutils.SimpleUtils;
import njust.edu.entity.*;
import njust.edu.web.Cart;
import njust.edu.web.Page;

import javax.servlet.ServletContext;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class TradeDaoImpl implements TradeDao {

//    static private Integer num =  0;
    @Override
    public Trade findById(Long id) {

        String sql = "select id,dish_num,price,userId,customer_table,date,state,staff_Id from trade where id = ?";
        Trade trade = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            while(rs.next()){
                trade  = new Trade(rs.getLong(1)
                        ,rs.getInt(2)
                        ,rs.getFloat(3)
                        ,rs.getLong(4)
                        ,rs.getInt(5)
                        ,rs.getTimestamp(6)
                        ,rs.getInt(7)
                        ,rs.getLong(8));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return trade;
    }


    @Override
    public void update(Trade trade) {
        String sql = "update trade set dish_num=?,price=?,userId=?,customer_table=?,date=?,state=?,staff_ID=? where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, trade.getDish_num());
            ps.setFloat(2,trade.getPrice());
            ps.setLong(3,trade.getUserId());
            ps.setInt(4,trade.getCustomer_table());
            ps.setTimestamp(5,trade.getDate());
            ps.setInt(6,trade.getState());
            ps.setLong(7,trade.getStaff_Id());
            ps.setLong(8,trade.getId());
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return ;
    }


    @Override
    public void delete(Long id) {
        String sql =  "delete from trade where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        SimpleUtils.release(conn,ps,rs);
        return;
    }
    @Override
    public Long add(Trade trade) {

        String sql = "insert into trade(dish_num,price,userId,customer_table,date,state,id) "
                + "values(?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Long ID = trade.getDate().getTime()%1000000000;
        try {
//            num++;
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, trade.getDish_num());
            ps.setFloat(2,trade.getPrice());
            ps.setLong(3,trade.getUserId());
            ps.setInt(4,trade.getCustomer_table());
            ps.setTimestamp(5,trade.getDate());
            ps.setInt(6,trade.getState());
            ps.setLong(7,ID);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return ID;
    }

    @Override
    public List<Trade> getList() {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
   public long getTotalUserNumber(Integer state) {
        String sql = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        long count = 0;
        if (state == null) {
            sql = "SELECT count(id) FROM trade";
            try {
                conn = SimpleUtils.getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                if(rs.next()){
                    count = rs.getInt(1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            sql = "SELECT count(id) FROM trade where state < ?";
            try {
                conn = SimpleUtils.getConnection();
                ps = conn.prepareStatement(sql);
                ps.setLong(1, state);
                rs = ps.executeQuery();
                if(rs.next()){
                    count = rs.getInt(1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        SimpleUtils.release(conn,ps,rs);
        return count;
  }

    //@Override
    public List<Trade> getPageList(int n, int pageSize, Integer state) {
        String sql = null;
        List<Trade> Trade_list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        if (state == null) {
            sql = "SELECT id,dish_num,price,userId,customer_table,date,state,staff_Id "
                    + "FROM trade order by date desc LIMIT ?, ?";
            try {
                conn = SimpleUtils.getConnection();
                ps = conn.prepareStatement(sql);
                ps.setInt(1, (n - 1) * pageSize);
                ps.setInt(2, pageSize);
                rs = ps.executeQuery();
                while(rs.next()){
                    Trade trade  = new Trade(rs.getLong(1)
                            ,rs.getInt(2)
                            ,rs.getFloat(3)
                            ,rs.getLong(4)
                            ,rs.getInt(5)
                            ,rs.getTimestamp(6)
                            ,rs.getInt(7)
                            ,rs.getLong(8));
                    Trade_list.add(trade);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            SimpleUtils.release(conn,ps,rs);
            return Trade_list;
        } else {
            sql = "SELECT id,dish_num,price,userId,customer_table,date,state,staff_Id "
                    + "FROM trade where state < ? order by date desc LIMIT ?, ?";
            try {
                conn = SimpleUtils.getConnection();
                ps = conn.prepareStatement(sql);
                ps.setInt(1, state);
                ps.setInt(2, (n - 1) * pageSize);
                ps.setInt(3, pageSize);
                rs = ps.executeQuery();
                while(rs.next()){
                    Trade trade  = new Trade(rs.getLong(1)
                            ,rs.getInt(2)
                            ,rs.getFloat(3)
                            ,rs.getLong(4)
                            ,rs.getInt(5)
                            ,rs.getTimestamp(6)
                            ,rs.getInt(7)
                            ,rs.getLong(8));
                    Trade_list.add(trade);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            SimpleUtils.release(conn,ps,rs);
            return Trade_list;
        }

    }

   // @Override
    public Page<Trade> getPage(int n, Integer state) {
        Page<Trade> page = new Page<Trade>(n);
        page.setTotalItemNumber(getTotalUserNumber(state));
        n = page.getPageNo();
        page.setList(getPageList(n, 10, state));//pagesize设置为10
        return page;
    }

    @Override
    public Double getUserMonthSalary(Long id, Date dateFrom,Date dateTo) {
        String sql = "select sum(price) from trade where  staff_Id= ? and Date(date) > ? and Date(date) < ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Double count = 0d;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            ps.setDate(2,dateFrom);
            ps.setDate(3,dateTo);
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getDouble(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return count;
    }

    @Override
    public Double getUserDaySalary(Long id,Date date) {
        String sql = "select sum(price) from trade where  staff_Id= ? and DATE (date)=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Double count = 0d;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            ps.setDate(2,date);
            //ps.setDate(3,date);
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getDouble(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return count;
    }

    @Override
    public double getUserDaySalary(Timestamp dateFrom, Timestamp dateTo) {
        String sql = "select sum(price) from trade where date >= ? and date < ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Double count = 0d;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setTimestamp(1,dateFrom);
            ps.setTimestamp(2,dateTo);
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getDouble(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return count;
    }

}
